/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package tuitionmanagement.DAO;

import tuitionmanagement.util.PassEncrypt;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import tuitionmanagement.entity.Function;
import tuitionmanagement.util.DBUtil;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * @author lab
 */
public class FunctionDAO {
    private Connection con;
    //private Connect db;
   // private DBUtil db=new DBUtil();
    private PassEncrypt pe;
    private ResultSet rs;
    private CallableStatement cst;
    private PreparedStatement pst;
    private String lastError;
    private final String SQL_CREATE;
    private final String SQL_READ;
    private final String SQL_UPDATE;
    private final String SQL_DELETE;

    
    public FunctionDAO(){
        con = null;
        pe = new PassEncrypt();
        rs = null;
        pst = null;
        lastError = "";
        SQL_CREATE = "INSERT INTO [Function](FunctionId,FunctionName,Description) VALUES(?, ?, ?)";
        SQL_READ = "SELECT * FROM [Function]";
        SQL_UPDATE = "Update  [Function] set FunctionName=? WHERE FunctionId = ?";
        SQL_DELETE = "DELETE FROM [Function] WHERE FunctionId = ?";
       
    }

    /**
     * Get Account list
     * @return ArrayList<Account>
     */
    @SuppressWarnings("static-access")
    public ArrayList<Function> readByAll() {
        ArrayList<Function> list = new ArrayList<Function>();
        con = DBUtil.getConn();
        try {
            pst = con.prepareStatement(SQL_READ);
            rs = pst.executeQuery();
            while (rs.next()) {
                Function b = new Function();
                b.setFunctionId(rs.getInt(1));
                b.setFunctionName(rs.getString(2));
                b.setDescription(rs.getString(3));
                

                list.add(b);
            }
        } catch (SQLException ex) {
            setLastError(ex.getMessage());
        }

        return list;
    }

    /**
     * Check whether Account is exist?
     * @param b
     * @return boolean
     */
    public boolean check(String username) {
        ArrayList<Function> list = readByAll();
        for (Function acc : list) {
            if (acc.getFunctionName().equalsIgnoreCase(username)) {
                setLastError("Existed! Please add another!");
                return false;
            }
        }
        return true;
    }

    /**
     * Add a new Account
     * @param Account
     * @return
     */
    public boolean create(Function acc) {
       
        if (!check(acc.getFunctionName())) {
            return false;
        }
        try {
            con = DBUtil.getConn();
            pst = con.prepareStatement(SQL_CREATE);
            pst.setInt(1, acc.getFunctionId());
            pst.setString(2, acc.getFunctionName());
            pst.setString(3, acc.getDescription());
            if (pst.executeUpdate() > 0) {
                setLastError("Add successfully!");
                return true;
            }
        } catch (SQLException ex) {
            setLastError(ex.getMessage());
        } finally {
            DBUtil.closeConn(con);
        }
        return false;
    }

    /**
     * Update Account information
     * @param b
     * @return
     */
    public boolean update(Function acc) {
       
        con = DBUtil.getConn();
        try {
             int effectedRows = 0;
            pst = con.prepareStatement(SQL_UPDATE, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            pst.setString(1, acc.getFunctionName());
            pst.setInt(2, acc.getFunctionId());
            effectedRows = pst.executeUpdate();
            setLastError("Update Account successfully!");
            if (effectedRows > 0) {
                return true;
            }
        } catch (SQLException ex) {
            setLastError(ex.getMessage());
        } finally {
            DBUtil.closeConn(con);
        }
        return false;
    }

    /**
     * Delete a Account
     * @param username
     * @return
     */
//<<<<<<< .mine
//    public boolean delete(int funId) {
//=======
//    public boolean delete(int functionId) {
//>>>>>>> .r85
//        con = DBUtil.getConn();
//        try {
//            pst = con.prepareStatement(SQL_DELETE, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
//<<<<<<< .mine
//            pst.setInt(1, funId);
//=======
//            pst.setInt(1, functionId);
//>>>>>>> .r85
//            if (pst.executeUpdate() > 0) {
//                setLastError("Delete  successfully!");
//                return true;
//            }
//        } catch (SQLException ex) {
//            setLastError(ex.getMessage());
//        } finally {
//            DBUtil.closeConn(con);
//        }
//        return false;
//    }

    /**
     * Check for login
     * @param acc
     * @return boolean
     */
        

    /**
     * Charge password for current account
     * @param acc
     * @param oldPass
     * @return boolean
     */   

    /**
     * @return the lastError
     */
    public String getLastError() {
        return lastError;
    }

    /**
     * @param lastError the lastError to set
     */
    public void setLastError(String lastError) {
        this.lastError = lastError;
    }


}

